IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveDelegateForSupervisor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RemoveDelegateForSupervisor]
GO
 
SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************              
* Name:   [RemoveDelegateForSupervisor]              
* Purpose:  Removes the delegate(s) for the supervisor, by setting the removed flag to 1                
* PARAMETERS(IN)              
* Name          Description                   
* -------------      -------------------------------------------              
* @xml       xml Document containing the hierarchy(REQUIRED)        
 The passed in XML has the following form:      
  <NewDataSet>      
   <Table>      
    <ParentId>234711</ParentId>   
 <ChildId>78543</ChildId>  
 <Removed>0</Removed>    
    </Table>      
   <Table>      
    <ParentId>185678</ParentId>   
 <ChildId>67543</ChildId>  
 <Removed>0</Removed>      
   </Table>      
   ...      
  </NewDataSet>  

* @supervisorId userId for whom the delegate(s) has to be removed  
                 
*********************************************************************/   
CREATE PROCEDURE dbo.[RemoveDelegateForSupervisor]
(
	@xml varchar(max),
	@supervisorId int
)
AS BEGIN

 declare @xmlDoc int        
 declare @delegateIds table (Sequence int identity, DelegateId int)        
 exec sp_xml_preparedocument @xmlDoc output, @xml        
 insert into @delegateIds select [Id] from openxml(@xmlDoc, '//IdList', 2) with (Id int)        
 exec sp_xml_removedocument @xmlDoc    

	update COI_Delegate_Map set Removed = 1 
	where SupervisorId = @supervisorId 
	and DelegateId in (Select DelegateId from @delegateIds)	

END